using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace supermarket.method
{
    public static class ExcelHelperNew
    {
        public static DataTable ImportFromExcel(string filePath)
        {
            try
            {
                DataTable dataTable = new DataTable();
                IWorkbook workbook;

                using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (Path.GetExtension(filePath).ToLower() == ".xlsx")
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                }

                ISheet sheet = workbook.GetSheetAt(0);
                IRow headerRow = sheet.GetRow(0);

                // 创建列
                for (int i = 0; i < headerRow.LastCellNum; i++)
                {
                    ICell cell = headerRow.GetCell(i);
                    string columnName = cell?.ToString() ?? $"Column{i + 1}";
                    dataTable.Columns.Add(columnName);
                }

                // 读取数据
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;

                    DataRow dataRow = dataTable.NewRow();
                    for (int j = 0; j < headerRow.LastCellNum; j++)
                    {
                        ICell cell = row.GetCell(j);
                        //dataRow[j] = cell?.ToString() ?? DBNull.Value;
                        dataRow[j] = cell?.ToString() ?? (object)DBNull.Value;
                    }
                    dataTable.Rows.Add(dataRow);
                }

                return dataTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"导入Excel文件时发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }

        public static bool ExportToExcel(DataTable dataTable, string filePath)
        {
            try
            {
                IWorkbook workbook;
                if (Path.GetExtension(filePath).ToLower() == ".xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else
                {
                    workbook = new HSSFWorkbook();
                }

                ISheet sheet = workbook.CreateSheet("Sheet1");

                // 创建标题行
                IRow headerRow = sheet.CreateRow(0);
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
                }

                // 写入数据
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
                    }
                }

                // 自动调整列宽
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                // 保存文件
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                }

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"导出Excel文件时发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
    }
} 